/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.aptech.dao;

import com.aptech.beans.Orders;
import com.aptech.beans.Users;
import com.aptech.forms.phthang.OrdersList;
import com.aptech.utility.DBType;
import com.aptech.utility.DBUtil;
import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author Huu Thang
 */
public class OrderManager {

    public static ResultSet displayAllRows() throws SQLException {
        String sql = "SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate,Orders.ShippedDate, Orders.SubTotal, Orders.Status "
                + "FROM Orders "
                + "INNER JOIN Customers "
                + " ON Orders.CustomerID=Customers.CustomerID";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static int selectByOrderIDDrugID(Orders order) throws SQLException {
        String sql = "DELETE FROM OrderDetails WHERE OrderID = ? AND DrugID = ?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

        preparedStatement.setInt(1, order.getOrderID());
        preparedStatement.setInt(2, order.getDrugID());

        int resultSet = preparedStatement.executeUpdate();
        if (resultSet == 1) {
            System.out.println("found");
        }
        return resultSet;
    }

    public static ResultSet getCustomerinfo(Orders order) throws SQLException {
        String sql = "SELECT Orders.OrderID, Customers.CustomerID, Customers.CustomerName, Customers.CustomerEmail, Customers.CustomerAddress, Customers.CustomerPhone, Customers.IsRelationCustomer, Orders.Subtotal"
                + " FROM Orders "
                + " INNER JOIN Customers "
                + " ON Orders.CustomerID=Customers.CustomerID "
                + " AND Orders.OrderID=?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setInt(1, order.getOrderID());
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static ResultSet getDrugOrderDetail(int id) throws SQLException {
        String sql = "SELECT Orders.OrderID, OrderDetails.DrugID, Drugs.DrugName, OrderDetails.UnitPrice, OrderDetails.Quantity"
                + " FROM Orders "
                + " INNER JOIN OrderDetails "
                + " ON Orders.OrderID=OrderDetails.OrderID "
                + " INNER JOIN Drugs "
                + " ON OrderDetails.DrugID=Drugs.DrugID"
                + " AND Orders.OrderID=?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setInt(1, id);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static ResultSet displayOrder() throws SQLException {
        String sql = "SELECT Orders.OrderID"
                + ",Customers.CustomerName"
                + ",Orders.OrderDate"
                + ",Orders.ShippedDate"
                + ",Orders.SubTotal"
                + ",Orders.Status"
                + " FROM Orders"
                + " INNER JOIN Customers"
                + " ON ORDERS.CustomerID = Customers.CustomerID";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

    public static boolean insert(Orders order) throws SQLException {

        String sql = "INSERT INTO Orders (CustomerID, OrderDate, ShippedDate, SubTotal, Status) VALUES (?, ?, ?, ?, ?)";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        conn.setAutoCommit(false);
        PreparedStatement pstm_Insert1 = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        //System.out.println(user.getUserID());
        pstm_Insert1.setInt(1, order.getCustomerID());
        pstm_Insert1.setTimestamp(2, order.getOrderDate());
        pstm_Insert1.setTimestamp(3, order.getShippedDate());
        pstm_Insert1.setDouble(4, order.getSubTotal());
        pstm_Insert1.setString(5, order.getStatus());

        int affected = pstm_Insert1.executeUpdate();
        conn.commit();
        return affected == 1;
    }

    public static ResultSet getOrderID() throws SQLException {

        String sql2 = "SELECT MAX(OrderID) FROM Orders";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql2,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        ResultSet resultSet = preparedStatement.executeQuery();

        if (resultSet.next()) {

            return resultSet;
        } else {
            return null;
        }

    }

    public static boolean insertNew(Orders order) throws SQLException {

        String sql1 = "INSERT INTO Orders (CustomerID, OrderDate, ShippedDate, SubTotal, Status) VALUES (?, ?, ?, ?, ?)";
        String sql2 = "SELECT Orders.OrderID FROM Orders WHERE Orders.OrderID = (SELECT MAX(Orders.OrderID) FROM Orders)";
        String sql3 = "INSERT INTO OrderDetails (OrderID, DrugID, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?)";
        Connection conn = DBUtil.getConnection(DBType.myConnection);
//        conn.setAutoCommit(false);

        PreparedStatement pstm_Insert1 = conn.prepareStatement(sql1,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
//        System.out.println(user.getUserID());
        pstm_Insert1.setInt(1, order.getCustomerID());
        pstm_Insert1.setTimestamp(2, order.getOrderDate());
        pstm_Insert1.setTimestamp(3, order.getShippedDate());
        pstm_Insert1.setDouble(4, order.getSubTotal());
        pstm_Insert1.setString(5, order.getStatus());
        int affected = pstm_Insert1.executeUpdate();
        int affected2 = 0;
        if (affected != 0) {

            PreparedStatement pstm_Select = conn.prepareStatement(sql2,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            ResultSet resultSet = pstm_Select.executeQuery();
            if (resultSet.next()) {
                PreparedStatement pstm_Insert2 = conn.prepareStatement(sql3,
                        ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

                pstm_Insert2.setInt(1, resultSet.getInt(1));
                pstm_Insert2.setInt(2, order.getDrugID());
                pstm_Insert2.setDouble(3, order.getUnitPrice());
                pstm_Insert2.setInt(4, order.getQuantity());
                pstm_Insert2.setDouble(5, order.getDiscount());
                affected2 = pstm_Insert2.executeUpdate();
            }
        }
//        conn.commit(); //transaction block end
        return affected == 1 && affected2 == 1;
    }

    public static boolean insertOrderDetail(Orders order, int id) throws SQLException {

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        conn.setAutoCommit(false);

        String sqlInsert = "INSERT INTO OrderDetails (OrderID, DrugID, UnitPrice, Quantity, Discount) VALUES (?, ?, ?, ?, ?)";
        PreparedStatement pstm_Insert2 = conn.prepareStatement(sqlInsert,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

        pstm_Insert2.setInt(1, id);
        pstm_Insert2.setInt(2, order.getDrugID());
        pstm_Insert2.setDouble(3, order.getUnitPrice());
        pstm_Insert2.setInt(4, order.getQuantity());
        pstm_Insert2.setDouble(5, order.getDiscount());
        int affected = pstm_Insert2.executeUpdate();
        conn.commit();

        return affected == 1;
    }

    public static boolean updateOrderByID(int quantity, int id, int drugid) throws SQLException {

        String sql_Update = "UPDATE OrderDetails"
                + " SET Quantity = ?"
                + " WHERE OrderID = ? AND DrugID=?";
        String sql_UpdatePrice = "UPDATE Orders"
                + " SET SubTotal = ?"
                + " WHERE OrderID = ?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);

        int affected = 0;

        PreparedStatement pstm_Update = conn.prepareStatement(sql_Update,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        pstm_Update.setInt(1, quantity);
        pstm_Update.setInt(2, id);
        pstm_Update.setInt(3, drugid);
        affected = pstm_Update.executeUpdate();
        if (affected == 1) {
            PreparedStatement Price = conn.prepareStatement(sql_UpdatePrice,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);

            pstm_Update.setInt(2, id);
            affected = pstm_Update.executeUpdate();
            return true;
        }
        return false;
    }

    public static boolean updateOrderByID(int quantity, int id, int drugid, double subtotal, String status) throws SQLException {

        String sql_Update = "UPDATE OrderDetails"
                + " SET Quantity = ?"
                + " WHERE OrderID = ? AND DrugID=?";
        String sql_UpdatePrice = "UPDATE Orders"
                + " SET SubTotal = ?, Status=?"
                + " WHERE OrderID = ?";
        Connection conn = DBUtil.getConnection(DBType.myConnection);

        int affected = 0;
        int affected2 = 0;
        PreparedStatement pstm_Update = conn.prepareStatement(sql_Update,
                ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
        pstm_Update.setInt(1, quantity);
        pstm_Update.setInt(2, id);
        pstm_Update.setInt(3, drugid);
        affected = pstm_Update.executeUpdate();
        if (affected == 1) {
            PreparedStatement pstm_Price = conn.prepareStatement(sql_UpdatePrice,
                    ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            pstm_Price.setDouble(1, subtotal);
            pstm_Price.setInt(3, id);
            pstm_Price.setString(2, status);
            affected2 = pstm_Price.executeUpdate();
            if (affected2 == 1) {
                return true;
            }
        }
        return false;
    }

    public static boolean updateStock(int stockAvailable, int drugID) {
        int affected = 0;
        try {
            String sql = "UPDATE Drugs SET StockAvailable=StockAvailable-? WHERE DrugID=?";

            Connection conn = DBUtil.getConnection(DBType.myConnection);
            PreparedStatement preparedStatement = conn.prepareStatement(sql,
                    ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);

            preparedStatement.setInt(1, stockAvailable);
            preparedStatement.setInt(2, drugID);

            affected = preparedStatement.executeUpdate();

        } catch (SQLException ex) {
            Logger.getLogger(OrderManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return affected == 1;
    }

    public static ResultSet selectStockAvailable(int drugid) throws SQLException {
        String sql = "SELECT StockAvailable"
                + " FROM Drugs "
                + " WHERE DrugID = ?";

        Connection conn = DBUtil.getConnection(DBType.myConnection);
        PreparedStatement preparedStatement = conn.prepareStatement(sql,
                ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        preparedStatement.setInt(1, drugid);
        ResultSet resultSet = preparedStatement.executeQuery();
        return resultSet;
    }

}
